﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IDAL;

namespace KPIS.GERP.GHRMS.DAL
{
    public sealed class HisSalaryDAL
    {
        private IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        private _AutoDBM autoDbm;

        public HisSalaryDAL()
        {
            autoDbm = new _AutoDBM(ref dbManager);
        }
        
        public DataSet LoadHisSalary(Nullable<int> Sequence, Nullable<int> Employee)
        {
            string strSQL;
            DataSet ds = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@Employee", Employee);
                dbManager.AddParameters(2, "@ActiveRecord", SystemInfo._ActiveRecordStatus);

				strSQL = "SELECT @Employee as CURRENT_EMPLOYEE_SEQ, h.*, m.MOVE_NAME "
                    + " FROM HRM_PS_EMP_SALARY_HISTORIES h"
                    + " LEFT JOIN HRM_PS_EMP_MOVMENTS m ON h.MOVE_SEQ = m.MOVE_SEQ"
                    + " WHERE h.RECORD_STATUS = @ActiveRecord"
                    + (Sequence.HasValue ? " AND h.SAL_HIS_SEQ = @Sequence" : "")
                    + (Employee.HasValue ? " AND h.EMP_SEQ = @Employee" : "")
                    + " ORDER BY h.START_DATE DESC";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }

            return ds;
        }
        public void UpdateHisSalary(object hisSalaryInfo)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(28);

                HisSalaryInfo hisSalaryInfoObj = (HisSalaryInfo)hisSalaryInfo;
                dbManager.AddParameters(0, "@emp_seq", hisSalaryInfoObj.emp_seq);
                dbManager.AddParameters(1, "@start_date", hisSalaryInfoObj.start_date);
                dbManager.AddParameters(2, "@end_date", hisSalaryInfoObj.end_date);
                dbManager.AddParameters(3, "@cmd_ref_no", hisSalaryInfoObj.cmd_ref_no);
                dbManager.AddParameters(4, "@cmd_name", hisSalaryInfoObj.cmd_name);

                dbManager.AddParameters(5, "@cmd_date", hisSalaryInfoObj.cmd_date);
                dbManager.AddParameters(6, "@pos_ref", hisSalaryInfoObj.pos_ref);
                dbManager.AddParameters(7, "@pos_name", hisSalaryInfoObj.pos_name);
                dbManager.AddParameters(8, "@class_seq", hisSalaryInfoObj.class_seq);
                dbManager.AddParameters(9, "@class_name", hisSalaryInfoObj.class_name);


                dbManager.AddParameters(10, "@job_site_seq", hisSalaryInfoObj.job_site_seq);
                dbManager.AddParameters(11, "@job_site_name", hisSalaryInfoObj.job_site_name);
                dbManager.AddParameters(12, "@salary_new", hisSalaryInfoObj.salary_new);
                dbManager.AddParameters(13, "@salary_old", hisSalaryInfoObj.salary_old);
                dbManager.AddParameters(14, "@salary_percent_up", hisSalaryInfoObj.salary_percent_up);

                dbManager.AddParameters(15, "@salary_up", hisSalaryInfoObj.salary_up);
                dbManager.AddParameters(16, "@salary_midpoint", hisSalaryInfoObj.salary_midpoint);
                dbManager.AddParameters(17, "@salary_extra", hisSalaryInfoObj.salary_extra);
                dbManager.AddParameters(18, "@salary_extra_seq", hisSalaryInfoObj.salary_extra_seq);
                dbManager.AddParameters(19, "@total_score", hisSalaryInfoObj.total_score);

                dbManager.AddParameters(20, "@move_seq", hisSalaryInfoObj.move_seq);
                dbManager.AddParameters(21, "@remarks", hisSalaryInfoObj.remarks);
                dbManager.AddParameters(22, "@record_status", 'U');
                dbManager.AddParameters(23, "@created_by", hisSalaryInfoObj.created_by);
                dbManager.AddParameters(24, "@created_when", hisSalaryInfoObj.created_when);

                dbManager.AddParameters(25, "@updated_by", hisSalaryInfoObj.updated_by);
                dbManager.AddParameters(26, "@updated_when", hisSalaryInfoObj.updated_when);
                dbManager.AddParameters(27, "@sal_his_seq", hisSalaryInfoObj.sal_his_seq);

                string strSQL = @" UPDATE hrm_ps_emp_salary_histories 
								SET
								emp_seq = @emp_seq , 
								start_date = @start_date, 
								end_date = @end_date, 
								cmd_ref_no = @cmd_ref_no, 
								cmd_name = @cmd_name, 
								cmd_date = @cmd_date, 
								pos_ref = @pos_ref, 
								pos_name = @pos_name, 
								class_seq = @class_seq, 
								class_name = @class_name, 
								job_site_seq = @job_site_seq, 
								job_site_name = @job_site_name, 
								salary_new = @salary_new, 
								salary_old = @salary_old, 
								salary_percent_up = ROUND(@salary_percent_up/1.15, 2),  
								salary_up = @salary_up, 
								salary_midpoint = @salary_midpoint, 
								salary_extra = @salary_extra, 
								salary_extra_seq = @salary_extra_seq, 
								total_score = @total_score, 
								move_seq = @move_seq, 
								remarks = @remarks, 
								record_status = @record_status, 
								created_by = @created_by, 
								created_when = @created_when, 
								updated_by = @updated_by, 
								updated_when = @updated_when
	
								WHERE
								sal_his_seq = @sal_his_seq ;
							"; 

                dbManager.ExecuteDataSet(CommandType.Text, strSQL);


            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public void InsertHisSalary(object hisSalaryInfo)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(27);

                HisSalaryInfo hisSalaryInfoObj = (HisSalaryInfo)hisSalaryInfo;
                dbManager.AddParameters(0, "@emp_seq", hisSalaryInfoObj.emp_seq);
                dbManager.AddParameters(1, "@start_date", hisSalaryInfoObj.start_date);
                dbManager.AddParameters(2, "@end_date", hisSalaryInfoObj.end_date);
                dbManager.AddParameters(3, "@cmd_ref_no", hisSalaryInfoObj.cmd_ref_no);
                dbManager.AddParameters(4, "@cmd_name", hisSalaryInfoObj.cmd_name);

                dbManager.AddParameters(5, "@cmd_date", hisSalaryInfoObj.cmd_date);
                dbManager.AddParameters(6, "@pos_ref", hisSalaryInfoObj.pos_ref);
                dbManager.AddParameters(7, "@pos_name", hisSalaryInfoObj.pos_name);
                dbManager.AddParameters(8, "@class_seq", hisSalaryInfoObj.class_seq);
                dbManager.AddParameters(9, "@class_name", hisSalaryInfoObj.class_name);


                dbManager.AddParameters(10, "@job_site_seq", hisSalaryInfoObj.job_site_seq);
                dbManager.AddParameters(11, "@job_site_name", hisSalaryInfoObj.job_site_name);
                dbManager.AddParameters(12, "@salary_new", hisSalaryInfoObj.salary_new);
                dbManager.AddParameters(13, "@salary_old", hisSalaryInfoObj.salary_old);
                dbManager.AddParameters(14, "@salary_percent_up", hisSalaryInfoObj.salary_percent_up);

                dbManager.AddParameters(15, "@salary_up", hisSalaryInfoObj.salary_up);
                dbManager.AddParameters(16, "@salary_midpoint", hisSalaryInfoObj.salary_midpoint);
                dbManager.AddParameters(17, "@salary_extra", hisSalaryInfoObj.salary_extra);
                dbManager.AddParameters(18, "@salary_extra_seq", hisSalaryInfoObj.salary_extra_seq);
                dbManager.AddParameters(19, "@total_score", hisSalaryInfoObj.total_score);

                dbManager.AddParameters(20, "@move_seq", hisSalaryInfoObj.move_seq);
                dbManager.AddParameters(21, "@remarks", hisSalaryInfoObj.remarks);
                dbManager.AddParameters(22, "@record_status", 'U');
                dbManager.AddParameters(23, "@created_by", hisSalaryInfoObj.created_by);
                dbManager.AddParameters(24, "@created_when", hisSalaryInfoObj.created_when);

                dbManager.AddParameters(25, "@updated_by", hisSalaryInfoObj.updated_by);
                dbManager.AddParameters(26, "@updated_when", hisSalaryInfoObj.updated_when);

                string strSQL = @"INSERT INTO hrm_ps_emp_salary_histories
                                        (emp_seq,
                                         start_date,
                                         end_date,
                                         cmd_ref_no,
                                         cmd_name,
                                         cmd_date,
                                         pos_ref,
                                         pos_name,
                                         class_seq,
                                         class_name,

                                         job_site_seq,
                                         job_site_name,
                                         salary_new,
                                         salary_old,
                                         salary_percent_up,
                                         salary_up,
                                         salary_midpoint,
                                         salary_extra,
                                         salary_extra_seq,
                                         total_score,
                                         move_seq,
                                         remarks,
                                         record_status,
                                         created_by,
                                         created_when,
                                         updated_by,
                                         updated_when)
                            VALUES (@emp_seq,
                                    @start_date,
                                    @end_date,
                                    @cmd_ref_no,
                                    @cmd_name,
                                    @cmd_date,
                                    @pos_ref,
                                    @pos_name,
                                    @class_seq,
                                    @class_name,
                                    @job_site_seq,
                                    @job_site_name,
                                    @salary_new,
                                    @salary_old,
                                    @salary_percent_up,
                                    @salary_up,
                                    @salary_midpoint,
                                    @salary_extra,
                                    @salary_extra_seq,
                                    @total_score,
                                    @move_seq,
                                    @remarks,
                                    @record_status,
                                    @created_by,
                                    @created_when,
                                    @updated_by,
                                    @updated_when);";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception error)
            {
                throw ;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public void DeleteHisSalary(int sequence, int deletedby)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@sal_his_seq", sequence);
                dbManager.AddParameters(1, "@updated_by", deletedby);
                dbManager.AddParameters(2, "@updated_when", DateTime.Now);


                string strSQL = @" UPDATE hrm_ps_emp_salary_histories
                                    SET record_status = 'D',
                                      updated_by = @updated_by,
                                      updated_when = @updated_when
                                    WHERE sal_his_seq = @sal_his_seq;";

                dbManager.ExecuteDataSet(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
    }
}
